package thitracnghiem;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Odotube
 */
public class Lop {

    int id;
    String lop;
    static Connection conn;
    static Statement stm;

    public Lop(int id, String lop) {
        this.id = id;
        this.lop = lop;
    }

    public static void Open() throws SQLException {
        conn = DriverManager.getConnection("jdbc:odbc:ttn");
        stm = conn.createStatement();
    }

    public static void Close() throws SQLException {
        stm.close();
        conn.close();
    }

    public static int insLop(String lop) throws SQLException {
        String sql = "insert into Lop values('" + lop + "')";
        return stm.executeUpdate(sql);
    }

    public static int delLop(String lop) throws SQLException {
        String sql = "delete Lop where lop='" + lop + "'";
        return stm.executeUpdate(sql);
    }

    public static ArrayList<String> getDSCacLop() throws SQLException {
        ArrayList<String> mList = new ArrayList<String>();
        String sql = "select * from Lop";
        ResultSet rs = stm.executeQuery(sql);
        while (rs.next()) {
            mList.add(rs.getString(2));
        }
        return mList;
    }

    public static ArrayList<String> getDSCacLop(String phanQuyenLop) throws SQLException {
        if (phanQuyenLop.equals("all")) {
            return Lop.getDSCacLop();
        } else {
            String[] strings = phanQuyenLop.split("//");
            if(strings.length==0) return null;
            ArrayList<String> mList = new ArrayList<String>();
            String sql = "select * from Lop where lop=";
            for(int i=0;i<strings.length;i++){
                sql+="'"+strings[i]+"'or lop=";
            }
            sql=sql.substring(0,sql.length()-7);
           // sql+=")";
            System.out.println(sql);
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                mList.add(rs.getString(2));
            }
            return mList;
        }
    }

    public static StringBuilder getViewDSCacLop(String phanQuyenLop) throws SQLException {
        StringBuilder ms = new StringBuilder();
        ArrayList<String> mList = Lop.getDSCacLop(phanQuyenLop);
        ms.append("<p align=center><table border=2><th>Lop<th>Xoa");
        for (int i = 0; i < mList.size(); i++) {
            ms.append("<tr><td>" + mList.get(i) + "</td><td><a href=/ThiTracNghiem/XuLy?yc=xoalop&lop=" + mList.get(i) + ">Xoa</td></tr>");
        }
        ms.append("</table></p>");
        return ms;

    }

    public static StringBuilder getDiemTheoDSLop(String lop, String tenMonThi, int lanThi) throws SQLException {
        StringBuilder ms = new StringBuilder();
        ArrayList<SinhVien> mDSLop = SinhVien.getDSTheoLop(lop);
        ms.append("<table border=2><th>MSSV<th>Ho va ten<th>Diem");
        if (lanThi == 1) {
            for (int i = 0; i < mDSLop.size(); i++) {
                ms.append("<tr><td width=200>").append(mDSLop.get(i).getMaSV()).append("</td>");
                ms.append("<td width=300>").append(mDSLop.get(i).getHoTenSV()).append("</td>");
                ms.append("<td width=200>").append(KetQuaThi.getDiemTheoMML(mDSLop.get(i).getMaSV(), tenMonThi, lanThi)).append("</td></tr>");
            }
        } else if (lanThi == 2) {
            for (int i = 0; i < mDSLop.size(); i++) {
                float f = KetQuaThi.getDiemTheoMML(mDSLop.get(i).getMaSV(), tenMonThi, lanThi);
                if (f != (float) -1.0) {
                    ms.append("<tr><td width=200>").append(mDSLop.get(i).getMaSV()).append("</td>");
                    ms.append("<td width=300>").append(mDSLop.get(i).getHoTenSV()).append("</td>");
                    ms.append("<td width=200>").append(KetQuaThi.getDiemTheoMML(mDSLop.get(i).getMaSV(), tenMonThi, lanThi)).append("</td></tr>");
                }
            }
        }
        return ms;
    }

    public void setID(int id) {
        this.id = id;
    }

    public int getID() {
        return this.id;
    }

    public void setLop(String lop) {
        this.lop = lop;
    }

    public String getLop() {
        return this.lop;
    }

    public static void main(String[] args) {
        try {
            Lop.Open();
            SinhVien.Open();
            String ms=new String(Lop.getDiemTheoDSLop("D09THA1", "TCC", 1));
            SinhVien.Close();
            Lop.Close();
        } catch (SQLException ex) {
            Logger.getLogger(Lop.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
